﻿Imports System.Data
Imports System.Data.OleDb
Imports System.Web.Configuration
Partial Class displaySong
    Inherits System.Web.UI.Page
    Public songID As Integer
    ''' <summary>
    ''' Populates the "comments" section of the page
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub populateCommentsSectionOfPage()
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        'populate comments gridview
        'declare and instantiate command object
        Dim myCommand3 = New OleDbCommand("SELECT * FROM commentsUserNickname WHERE moderatorFlag=0 AND songID=" & Me.songID & " ORDER BY commentDate DESC;")
        'open connection
        myConnection.Open()
        'set connection property of command object
        myCommand3.Connection = myConnection
        'declare reader
        Dim myReader3 As OleDbDataReader
        'execute reader
        myReader3 = myCommand3.ExecuteReader
        'set data source property of grid view and invoke dataBind method to fill control
        Me.GridView_comments.DataSource = myReader3
        Me.GridView_comments.DataBind()
        If Me.GridView_comments.Rows.Count = 0 Then
            Me.Literal_noComments.Visible = True 'make "no comments" message visible
        Else
            Me.Literal_noComments.Visible = False 'hide "no comments" message
        End If
        'close connection
        myConnection.Close()
    End Sub
    Sub populateTaggingText()
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        Dim myReader As OleDbDataReader
        Dim queryText As String = "SELECT ID, tagText FROM tagText WHERE songID=" & Me.songID & ";"
        Dim myCommand = New OleDbCommand(queryText)
        myConnection.Open()
        myCommand.Connection = myConnection
        myReader = myCommand.ExecuteReader
        myReader.Read()
        Dim commaDelineatedTagText As String = "<i>Tags: </i>"
        If myReader.HasRows Then 'we have some tags for the song, so serialise them and display as hyperlinks
            commaDelineatedTagText = commaDelineatedTagText & "<a href=""./tagging.aspx?ID=" & myReader("ID") & """>" & myReader("tagText") & "</a>;"
            Do While myReader.Read()
                commaDelineatedTagText = commaDelineatedTagText & "<a href=""./tagging.aspx?ID=" & myReader("ID") & """>" & myReader("tagText") & "</a>;"
            Loop
            Me.p_taggingText.InnerHtml = commaDelineatedTagText
        Else 'no tags found
            Me.p_taggingText.InnerHtml = "<i>Tags: </i>This song has not been tagged."
        End If
        myConnection.Close()
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'get song ID from query string
        If Request.QueryString("ID") Is Nothing Then
            'no ID in query string so redirect to home page
            Response.Redirect("~/home.aspx")
        Else
            If Not Regex.IsMatch(Request.QueryString("ID"), "^[0-9]{1,}$") Then 
                'ID in query string is not an integer so redirect to home page
                Response.Redirect("~/home.aspx")
            End If
            'set songID class member to song ID
            Me.songID = CInt(Request.QueryString("ID"))
        End If

        'set visibility of certain page content based on whether user is authenticated or not
        If Page.User.Identity.IsAuthenticated Then
            Me.div_loggedInContent.Visible = True
            Me.div_notLoggedInContent.Visible = False
            Me.div_addTagText.Visible = True
            Me.Button_editSong.Visible = True
        Else
            Me.div_loggedInContent.Visible = False
            Me.div_notLoggedInContent.Visible = True
            Me.div_addTagText.Visible = False
            Me.Button_editSong.Visible = False
        End If

        Dim myConnection = New OleDbConnection()
        Dim myReader1 As OleDbDataReader
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        Dim myCommand1 = New OleDbCommand("SELECT * FROM songDetails WHERE ID=" & Me.songID & ";")
        Try
            myConnection.Open()
            'set connection property of command object
            myCommand1.Connection = myConnection
            'execute reader
            myReader1 = myCommand1.ExecuteReader
            myReader1.Read()

            Dim title As String = ""
            Dim bandName As String = ""
            Dim genreName As String = ""

            'check that reader has rows
            If myReader1.HasRows Then
                'extract song data
                title = myReader1("title")
                bandName = myReader1("bandName")
                genreName = myReader1("genreName")
                Dim rating As String = myReader1("rating")
                Dim numberOfRatings As String = myReader1("numberOfRatings")
                Dim lyricsPlusChords As String = myReader1("lyricsPlusChords")
                'fill page elements
                Me.HiddenField_numberOfRatings.Value = numberOfRatings
                Me.HiddenField_rating.Value = rating
                Me.h2_title.InnerText = title
                Me.p_bandName.InnerHtml = "<i>Artist:</i> " & bandName
                Me.p_genreName.InnerHtml = "<i>Genre:</i> " & genreName
                Dim NoR As Integer = CInt(numberOfRatings)
                Select Case NoR
                    Case Is > 1
                        Me.p_ratingText.InnerHtml = "<i>Rating:</i> Average " & Math.Round(CDbl(rating), 1) & " out of 5, based on " & numberOfRatings & " ratings."
                    Case 1
                        Me.p_ratingText.InnerHtml = "<i>Rating:</i> " & Math.Round(CDbl(rating), 1) & " out of 5, by just one user."
                    Case Else
                        Me.p_ratingText.InnerHtml = "<i>Rating:</i> Not yet rated.  Be the first to rate this song!"
                End Select
                Me.div_songDisplay.InnerHtml = renderer.renderSong(Replace(lyricsPlusChords, Environment.NewLine, "<br/>"))
                myConnection.Close()
            Else
                myConnection.Close()
                'no rows, so redirect to home page
                Response.Redirect("~/home.aspx")
            End If
            populateCommentsSectionOfPage()
            populateTaggingText()
        Catch ex As Exception
            Response.Redirect("~/error.htm")
        Finally
            myConnection.Close()
        End Try
        
    End Sub

    Protected Sub Button_postComment_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_postComment.Click
        'hide comment text box, to deter the user from adding multiple comments
        Me.div_addComment.Visible = False
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        'declare and instantiate command object
        Dim myCommand_insertNewComment = New OleDbCommand("INSERT INTO comments (songID, userID, comment) VALUES (?, ?, ?);")
        myCommand_insertNewComment.Parameters.Add(New OleDbParameter("songID", Me.songID))
        myCommand_insertNewComment.Parameters.Add(New OleDbParameter("userID", util.getUserID()))
        myCommand_insertNewComment.Parameters.Add(New OleDbParameter("comment", Me.TextBox_comment.Text))
        Try
            'open connection
            myConnection.Open()
            'set connection property of command object
            myCommand_insertNewComment.Connection = myConnection
            'execute command to insert new comment
            myCommand_insertNewComment.ExecuteNonQuery()
            'close connection
            myConnection.Close()
        Catch ex As Exception
            'redirect to error message page
            Response.Redirect("~/error.htm")
        Finally
            myConnection.Close()
        End Try
        'refresh comments grid view
        populateCommentsSectionOfPage()
    End Sub

    Protected Sub DropDownList_rate_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList_rate.SelectedIndexChanged
        'hide rating drop down list (to deter the user from rating the song twice)
        Me.div_rateSong.Visible = False
        'get current rating and number of ratings
        Dim rating As Double = CDbl(Me.HiddenField_rating.Value)
        Dim numberOfRatings As Integer = CInt(Me.HiddenField_numberOfRatings.Value)
        'update mean rating
        rating = ((numberOfRatings * rating) + CInt(Me.DropDownList_rate.SelectedValue)) / (numberOfRatings + 1)
        'update number of ratings
        numberOfRatings = numberOfRatings + 1
        'update hidden fields
        Me.HiddenField_rating.Value = rating
        Me.HiddenField_numberOfRatings.Value = numberOfRatings
        'format nicely!
        Me.p_ratingText.Style.Add("color", "Red")
        Me.p_ratingText.InnerHtml = "<i>Rating:</i> " & Math.Round(rating, 1) & " out of 5.  Thank you for rating this song!"
        'write updated values to database
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        'declare and instantiate command object
        Dim myCommand_updateRatingInfo = New OleDbCommand("UPDATE songs SET rating=?, numberOfRatings=? WHERE ID=" & Me.songID)
        myCommand_updateRatingInfo.Parameters.Add(New OleDbParameter("rating", rating))
        myCommand_updateRatingInfo.Parameters.Add(New OleDbParameter("numberOfRatings", numberOfRatings))
        Try
            'open connection
            myConnection.Open()
            'set connection property of command object
            myCommand_updateRatingInfo.Connection = myConnection
            'execute command to insert new comment
            myCommand_updateRatingInfo.ExecuteNonQuery()
            'close connection
            myConnection.Close()
        Catch ex As Exception
            'redirect to error message page
            Response.Redirect("~/error.htm")
        Finally
            myConnection.Close()
        End Try

    End Sub

    Protected Sub GridView_comments_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView_comments.RowCommand
        'set moderator flag to true to make offending comment invisible until reviewed by moderator
        Dim offendingRowIndex As Integer = CInt(e.CommandArgument.ToString())
        Dim offendingCommentID As Integer = CInt(GridView_comments.DataKeys(offendingRowIndex).Value)
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        'declare and instantiate command object
        Dim myCommand_setModeratorFlag = New OleDbCommand("UPDATE comments SET moderatorFlag=1 WHERE ID=" & offendingCommentID)
        Try
            'open connection
            myConnection.Open()
            'set connection property of command object
            myCommand_setModeratorFlag.Connection = myConnection
            'execute command to insert new comment
            myCommand_setModeratorFlag.ExecuteNonQuery()
            'close connection
            myConnection.Close()
        Catch ex As Exception
            'redirect to error message page
            Response.Redirect("~/error.htm")
        Finally
            myConnection.Close()
        End Try
        'refresh grid view
        populateCommentsSectionOfPage()
    End Sub

    Protected Sub Button_addTagText_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_addTagText.Click
        If Trim(Me.TextBox_addTagText.Text = "") Then Exit Sub
        Dim tagID As Integer
        'create array of individual tags to be associated with songs in the songTags junction table
        Dim tags() As String = Split(Me.TextBox_addTagText.Text, ";")
        Dim myReader As OleDbDataReader
        Dim myConnection1 = New OleDbConnection()
        Dim myConnection2 = New OleDbConnection()
        Dim myConnection3 = New OleDbConnection()
        myConnection1.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        myConnection2.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        myConnection3.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString

        Dim myCommand_queryForTagText = New OleDbCommand("SELECT ID from tags WHERE tagText=?")
        Dim myCommand_addTagToSongTags = New OleDbCommand("INSERT INTO songTags (songID, tagID) VALUES (?, ?);")
        Dim myCommand_addTag = New OleDbCommand("INSERT INTO tags (tagText) VALUES (?);")
        Dim myCommand_getID = New OleDbCommand("SELECT @@Identity;")
        Dim myCommand_checkForDuplicate = New OleDbCommand("SELECT * from songTags WHERE songID=? AND tagID=?")

        myConnection1.Open()
        myConnection2.Open()
        myConnection3.Open()

        myCommand_queryForTagText.Connection = myConnection1
        myCommand_addTagToSongTags.Connection = myConnection2
        myCommand_addTag.Connection = myConnection3
        myCommand_getID.Connection = myConnection3
        myCommand_checkForDuplicate.Connection = myConnection3

        For Each tag As String In tags
            'clear parameters
            myCommand_queryForTagText.Parameters.Clear()
            myCommand_addTagToSongTags.Parameters.Clear()
            myCommand_addTag.Parameters.Clear()
            'strip away leading and trailing spaces
            tag = Trim(tag)
            If tag <> "" Then
                'query whether tag text is already in the database
                myCommand_queryForTagText.Parameters.Add(New OleDbParameter("tagText", tag))
                myReader = myCommand_queryForTagText.ExecuteReader
                myReader.Read()
                If myReader.HasRows Then
                    'tag text is already present - get its ID
                    tagID = CInt(myReader("ID"))
                Else
                    'tag text is not already present in tags table - add it and get its ID
                    myCommand_addTag.Parameters.Add(New OleDbParameter("tagText", tag))
                    myCommand_addTag.ExecuteNonQuery()
                    tagID = myCommand_getID.ExecuteScalar()
                End If
                myReader.Close()
                myReader = Nothing
                'check that linking record does not already exist
                myCommand_checkForDuplicate.Parameters.Add(New OleDbParameter("songID", Me.songID))
                myCommand_checkForDuplicate.Parameters.Add(New OleDbParameter("tagID", tagID))
                Dim dupFlag As Integer = myCommand_checkForDuplicate.ExecuteScalar()
                If dupFlag = 0 Then
                    'add record to songTags linking table
                    myCommand_addTagToSongTags.Parameters.Add(New OleDbParameter("songID", Me.songID))
                    myCommand_addTagToSongTags.Parameters.Add(New OleDbParameter("tagID", tagID))
                    myCommand_addTagToSongTags.ExecuteNonQuery()
                End If
            End If
        Next
        myConnection1.Close()
        myConnection2.Close()
        myConnection3.Close()
        populateTaggingText()
    End Sub

    Protected Sub Button_editSong_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_editSong.Click
        Response.Redirect("~/editSong.aspx?ID=" & Me.songID)
    End Sub
End Class
